package cn.edu.sdjzu.xg.dao;

import cn.edu.sdjzu.xg.domain.School;
import cn.edu.sdjzu.xg.domain.University;
import cn.edu.sdjzu.xg.exception.BysjException;
import cn.edu.sdjzu.xg.service.UniversityService;
import cn.edu.sdjzu.xg.util.Condition;
import cn.edu.sdjzu.xg.util.JdbcHelper;
import cn.edu.sdjzu.xg.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public class SchoolDao {
    private static SchoolDao schoolDao = null;
    private SchoolDao(){}
    public static SchoolDao getInstance(){
        if (schoolDao ==null){
            schoolDao =new SchoolDao();

        }
        return schoolDao;

    }
    public  int add(School school)throws SQLException {
        Connection connection = JdbcHelper.getConn();
        String sql_insert=  "insert into school(description,no,university_id)values"+
                "(?,?,?)";
        PreparedStatement preparedStatement_add= connection.prepareStatement(sql_insert);
        preparedStatement_add.setString(1,school.getDescription());
        preparedStatement_add.setString(2,school.getNo());
        preparedStatement_add.setInt(3,school.getUniversity().getId());
        int rowAffected= preparedStatement_add.executeUpdate();


        JdbcHelper.close(preparedStatement_add,connection);

        return rowAffected;

    }
    public boolean update(School school)throws SQLException{
        Connection connection = JdbcHelper.getConn();
        String sql_update = " update teacher set department=?,no=?,remarks=?,university_id=?"
                +" where id = ?";
        PreparedStatement pstmt_update = connection.prepareStatement(sql_update);
        pstmt_update.setString(1, school.getDescription());
        pstmt_update.setString(2, school.getNo());
        pstmt_update.setString(3,school.getRemarks());
        pstmt_update.setInt(4,school.getUniversity().getId());
        pstmt_update.setInt(5, school.getId());
        int rowAffected = pstmt_update.executeUpdate();
        JdbcHelper.close(pstmt_update,connection);
        return rowAffected>0;
    }
    public School find(Integer id)throws  SQLException {
        School desiredschool = null;
        Connection connection = JdbcHelper.getConn();
        String sql_select_id = "select * from school where id = ?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1, id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        if (resultSet_select_id.next()) {
            String description = resultSet_select_id.getString("description");
            String no = resultSet_select_id.getString("no");
            String remarks=resultSet_select_id.getString("remarks");
            Integer university_id=resultSet_select_id.getInt("university_id");
            University university= UniversityService.getInstance().find(university_id);
            desiredschool = new School(id, description, no,remarks,university);
        }
        JdbcHelper.close(resultSet_select_id, pstmt_select_id, connection);
        return desiredschool;

    }
    public Collection<School> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException , BysjException {
        Collection<School>desiredSchools=new TreeSet<>();
        StringBuilder sql_select = new StringBuilder("select * from school");
        if (conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
            System.out.println(sql_select);
        }

        int totalNum = CommonDao.getInstance().count(connection,"school",conditionList);
        String paginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(paginationClause);
        System.out.println(sql_select);
        PreparedStatement preparedStatement = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks=resultSet.getString("remarks");
            Integer university_id=resultSet.getInt("university_id");
            University university= UniversityService.getInstance().find(university_id);
            School desiredSchool = new School(id, description, no,remarks,university);
            desiredSchools.add(desiredSchool);

            System.out.println(desiredSchools);
        }
        JdbcHelper.close(resultSet,preparedStatement,connection);
        return desiredSchools;
    }
}
